package ConnectionDB;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import Model.ArvoreBean;
import Model.UsuarioBean;
import Model.Condicional;
import Model.Consulta;

public class SorocabaDAO {

    private ConnectionSorocabaFactory conexao;

    public SorocabaDAO() {
        this.conexao = new ConnectionSorocabaFactory();
    }

    public Connection getConnection() {
        return this.conexao.getConn();
    }

    public void closeConnection() {
        this.conexao.closeConection();
    }

    public int cadastrarUsuario(UsuarioBean usuario) {
        Statement stm = null;
        ResultSet rs = null;
        int sucesso = 0;
        String sql = "SELECT * FROM cadastrarUsuario('"
                + usuario.getNome() + "','"
                + usuario.getEmail() + "','"
                + usuario.getCidade() + "','"
                + usuario.getEstado() + "','"
                + usuario.getSenha() + "');";

        try {
            stm = this.conexao.getConn().createStatement();
            rs = stm.executeQuery(sql);
            rs.next();
            sucesso = rs.getInt(1);
        } catch (SQLException sqle) {
            sqle.printStackTrace();
        } finally {
            this.conexao.closeConection();
        }
        return sucesso;
    }

    public UsuarioBean logarUsuario(String email, String senha) {
        Statement stm = null;
        ResultSet rs = null;
        UsuarioBean usuario = new UsuarioBean();

        String sql = "SELECT * FROM logarUsuario('" + email + "','" + senha + "') AS ("
                + "nome_usuario varchar(30),"
                + "email varchar(100),"
                + "cidade varchar(30),"
                + "estado char(2),"
                + "tipo_acesso boolean);";

        try {
            stm = this.conexao.getConn().createStatement();
            rs = stm.executeQuery(sql);

            if (rs.next()) {
                usuario.setNome(rs.getString(1));
                usuario.setEmail(rs.getString(2));
                usuario.setCidade(rs.getString(3));
                usuario.setEstado(rs.getString(4));
                usuario.setTipoAcesso(rs.getBoolean(5));
            } else {
                usuario = null;
            }
        } catch (SQLException sqle) {
            sqle.printStackTrace();
        } finally {
            this.conexao.closeConection();
        }
        return usuario;
    }

    public UsuarioBean editarUsuario(UsuarioBean usuario) {
        Statement stm = null;
        ResultSet rs = null;
        UsuarioBean novoUsuario = new UsuarioBean();

        String sql = "SELECT * FROM editarUsuario('"
                + usuario.getNome() + "','"
                + usuario.getEmail() + "','"
                + usuario.getCidade() + "','"
                + usuario.getEstado() + "','"
                + usuario.getSenha() + "') AS ("
                + "nome_usuario varchar(30),"
                + "email varchar(100),"
                + "cidade varchar(30),"
                + "estado char(2),"
                + "tipo_acesso boolean);";

        try {
            stm = this.conexao.getConn().createStatement();
            rs = stm.executeQuery(sql);

            if (rs.next()) {
                novoUsuario.setNome(rs.getString(1));
                novoUsuario.setEmail(rs.getString(2));
                novoUsuario.setCidade(rs.getString(3));
                novoUsuario.setEstado(rs.getString(4));
                novoUsuario.setTipoAcesso(rs.getBoolean(5));
            } else {
                novoUsuario = null;
            }
        } catch (SQLException sqle) {
            sqle.printStackTrace();
        } finally {
            this.conexao.closeConection();
        }
        return novoUsuario;
    }

    public String infoBanco() {
        String infohtml = "", sql = "";

        Statement stm = null;
        ResultSet rs = null;

        try {
            sql = "SELECT * FROM numeroArvore();";
            stm = this.conexao.getConn().createStatement();
            rs = stm.executeQuery(sql);
            rs.next();
            infohtml = "<div class='infoBD'><p class='infoTitle'>"
                    + "<strong>Informações atuais da Base de Dados</strong></p>"
                    + "<p class='info'><strong>"
                    + rs.getString(1)
                    + "</strong> árvores catalogadas de</p><p class='info'><strong>";

            sql = "SELECT * FROM numeroGenero();";
            stm = this.conexao.getConn().createStatement();
            rs = stm.executeQuery(sql);
            rs.next();
            infohtml = infohtml
                    + rs.getString(1)
                    + "</strong> gêneros de</p><p class='info'><strong>";

            sql = "SELECT * FROM numeroEspecie();";
            stm = this.conexao.getConn().createStatement();
            rs = stm.executeQuery(sql);
            rs.next();
            infohtml = infohtml
                    + rs.getString(1)
                    + "</strong> espécies distintas e localizadas em</p><p class='info'><strong>";

            sql = "SELECT * FROM numeroRua();";
            stm = this.conexao.getConn().createStatement();
            rs = stm.executeQuery(sql);
            rs.next();
            infohtml = infohtml
                    + rs.getString(1)
                    + "</strong> ruas de</p><p class='info'><strong>";

            sql = "SELECT * FROM numeroBairro();";
            stm = this.conexao.getConn().createStatement();
            rs = stm.executeQuery(sql);
            rs.next();
            infohtml = infohtml
                    + rs.getString(1)
                    + "</strong> bairros de Sorocaba.</p></div>";


        } catch (SQLException sqle) {
            sqle.printStackTrace();
        } finally {
            this.conexao.closeConection();
        }
        return infohtml;
    }

    public List<String> listarBairro() {
        List<String> listaBairro = new ArrayList<String>();
        String sql = "";

        Statement stm = null;
        ResultSet rs = null;

        try {
            sql = "SELECT * FROM listarBairros() AS (nome_bairro VARCHAR(40));";
            stm = this.conexao.getConn().createStatement();
            rs = stm.executeQuery(sql);

            while (rs.next()) {
                listaBairro.add(rs.getString(1));
            }

        } catch (SQLException sqle) {
            sqle.printStackTrace();
        }
        return listaBairro;
    }

    public List<String> listarRua() {
        List<String> listaRua = new ArrayList<String>();
        String sql = "";

        Statement stm = null;
        ResultSet rs = null;

        try {
            sql = "SELECT * FROM listarRuas() AS (nome_rua VARCHAR(60));";
            stm = this.conexao.getConn().createStatement();
            rs = stm.executeQuery(sql);

            while (rs.next()) {
                listaRua.add(rs.getString(1));
            }

        } catch (SQLException sqle) {
            sqle.printStackTrace();
        }
        return listaRua;
    }

    public List<String> listarEspecie() {
        List<String> listaEspecie = new ArrayList<String>();
        String sql = "";

        Statement stm = null;
        ResultSet rs = null;

        try {
            sql = "SELECT * FROM listarEspecies() AS (nome_especie VARCHAR(50));";
            stm = this.conexao.getConn().createStatement();
            rs = stm.executeQuery(sql);

            while (rs.next()) {
                listaEspecie.add(rs.getString(1));
            }

        } catch (SQLException sqle) {
            sqle.printStackTrace();
        }
        return listaEspecie;
    }

    public List<String> listarGenero() {
        List<String> listaGenero = new ArrayList<String>();
        String sql = "";

        Statement stm = null;
        ResultSet rs = null;

        try {
            sql = "SELECT * FROM listarGeneros() AS (nome_genero VARCHAR(30));";
            stm = this.conexao.getConn().createStatement();
            rs = stm.executeQuery(sql);

            while (rs.next()) {
                listaGenero.add(rs.getString(1));
            }

        } catch (SQLException sqle) {
            sqle.printStackTrace();
        }
        return listaGenero;
    }

    public List<String> listarNomePopular() {
        List<String> listaNomePopular = new ArrayList<String>();
        String sql = "";

        Statement stm = null;
        ResultSet rs = null;

        try {
            sql = "SELECT * FROM listarNomesPopulares() AS (nome_popular VARCHAR(50));";
            stm = this.conexao.getConn().createStatement();
            rs = stm.executeQuery(sql);

            while (rs.next()) {
                listaNomePopular.add(rs.getString(1));
            }

        } catch (SQLException sqle) {
            sqle.printStackTrace();
        }
        return listaNomePopular;
    }

    public List<Consulta> listarConsultas() {
        List<Consulta> listaConsultas = null;
        List<Condicional> listaCondicional = null;

        String sql;
        Statement stm = null;
        ResultSet rs = null;

        try {
            sql = "SELECT * FROM listarConsultas() AS (id integer, nome varchar(100), descricao text, pre_script text, pos_script text);";
            stm = this.conexao.getConn().createStatement();
            rs = stm.executeQuery(sql);

            listaConsultas = new ArrayList<Consulta>();
            Consulta temp;
            while (rs.next()) {
                temp = new Consulta();
                temp.setIdConsulta(rs.getString(1));
                temp.setNomeConsulta(rs.getString(2));
                temp.setDescricao(rs.getString(3));
                temp.setPreScript(rs.getString(4));
                temp.setPosScript(rs.getString(5));
                listaConsultas.add(temp);
            }

            int index = 0;
            Condicional tempCond;
            while (index < listaConsultas.size()) {
                sql = "SELECT * FROM listarCondicional(" + listaConsultas.get(index).getIdConsulta() + ") AS (nome varchar(30), tag_html varchar(30), condicao varchar(50));";
                stm = this.conexao.getConn().createStatement();
                rs = stm.executeQuery(sql);

                listaCondicional = new ArrayList<Condicional>();
                while (rs.next()) {
                    tempCond = new Condicional();
                    tempCond.setNome(rs.getString(1));
                    tempCond.setTagHtml(rs.getString(2));
                    tempCond.setCondicao(rs.getString(3));
                    listaCondicional.add(tempCond);
                }
                listaConsultas.get(index).setListaCondicional(listaCondicional);
                index++;
            }

        } catch (SQLException sqle) {
            sqle.printStackTrace();
        }

        return listaConsultas;
    }

    public ResultSet executarConsulta(String query) {
        Statement stm = null;
        ResultSet rs = null;

        try {
            stm = this.conexao.getConn().createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
            rs = stm.executeQuery(query);

        } catch (SQLException sqle) {
            sqle.printStackTrace();
        } finally {
            this.conexao.closeConection();
        }
        return rs;
    }

    public ResultSet explorarCenso(
            String rua,
            String bairro,
            String especie,
            String nomePopular,
            String genero,
            String origem,
            String alturaTotal,
            String alturaBifurcacao,
            String raioCopaMinimo,
            String raioCopaMaximo,
            String areaCopaMinimo,
            String areaCopaMaximo,
            String dano,
            String tipoPlantio,
            String situacao,
            String matriz) {

        Statement stm = null;
        ResultSet rs = null;
        String query = "SELECT * FROM buscarArvoreCenso("
                + raioCopaMinimo + ","
                + raioCopaMaximo + ","
                + areaCopaMinimo + ","
                + areaCopaMaximo + ","
                + alturaTotal + ","
                + alturaBifurcacao + ","
                + tipoPlantio + ","
                + dano + ","
                + situacao + ","
                + matriz + ","
                + rua + ","
                + bairro + ","
                + especie + ","
                + nomePopular + ","
                + genero + ","
                + origem
                + ") AS ("
                + "\"Data da Coleta\"		date,"
                + "\"Ordem de Coleta\"		integer,"
                + "\"Logradouro\"			varchar(60),"
                + "\"Bairro\"			varchar(40),"
                + "\"Coletor\"			varchar(40),"
                + "\"Espécie\" 			varchar(50),"
                + "\"Nome Popular\"		varchar(50),"
                + "\"Gênero\"			varchar(30),"
                + "\"Origem\"			char,"
                + "\"Raio de Copa (m)\"		real,"
                + "\"Área de Copa (m²)\"		real,"
                + "\"Número Próximo\"		integer,"
                + "\"Posição\"			char,"
                + "\"Altura (m)\"			char,"
                + "\"Altura da Bifurcação (m)\"	char,"
                + "\"Tipo de Plantio\"		char,"
                + "\"Dano\"			char,"
                + "\"Situação\"			char,"
                + "\"Matriz\"			char);";

        try {
            stm = this.conexao.getConn().createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
            rs = stm.executeQuery(query);

        } catch (SQLException sqle) {
            sqle.printStackTrace();
        } finally {
            this.conexao.closeConection();
        }
        return rs;
    }
}